摘要

在資料庫應用與數據分析領域，將記錄導向的「長格式」（Long Format）資料轉換為欄位導向的「寬格式」（Wide Format）是一項基礎且關鍵的操作，此過程稱為「樞紐分析」或「資料透視」（Pivoting）。本文旨在深入探討 SQLite 的 pivot_vtab 虛擬資料表擴充功能，並透過在 Node.js 環境下的具體程式碼範例，闡述其如何以宣告式 SQL 實現高效、動態的資料透視，從而替代傳統的應用層邏輯或複雜的 CASE 陳述式。

一、背景：資料結構與分析需求

在進行資料分析時，原始數據常以長格式儲存，該格式具備高正規化、易於寫入的優點。考慮以下「銷售資料」表，其記錄了不同產品在各年度的銷售額。

表 1：銷售資料 原始結構

產品名稱	年份	銷售額
蘋果	2020	100
蘋果	2021	120
…	…	…
鳳梨	2020	10
…	…	…
葡萄	2020	80
…	…	…

此結構不利於直觀比較各產品的年度銷售趨勢。為滿足報表與分析需求，需將其轉換為寬格式，以「產品名稱」為列索引，以「年份」為欄，如下所示。

表 2：目標樞紐分析結構

產品名稱	2020	2021	2022	2023
蘋果	100	120	130	140
鳳梨	10	20	40	80
葡萄	80	75	78	80

pivot_vtab 擴充功能為此類轉換提供了一個高效能且語法簡潔的資料庫內建解決方案。

二、技術實現：Node.js 與 `pivot_vtab` 整合

下述程式碼展示了在 Node.js 環境中，如何利用 sqlite3 套件載入 pivot_vtab 擴充功能並執行樞紐分析。

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

// 載入 pivot_vtab 擴充功能
// 注意：檔案路徑與副檔名 (.dylib, .so, .dll) 需依作業系統調整
db.loadExtension('./pivotvtab.dylib', (err) => {
    if (err) {
        console.error("錯誤：無法載入 pivot_vtab 擴充功能。", err);
        return;
    }

    db.serialize(() => {
        // 步驟 1: 建立來源資料表並寫入資料
        db.run(`
            CREATE TABLE 銷售資料 (
                產品名稱 TEXT,
                年份     INTEGER,
                銷售額   INTEGER
            );
        `);
        // ... (資料 INSERT 陳述式與前例相同) ...
        const inserts = [
            ["蘋果", 2020, 100], ["蘋果", 2021, 120], ["蘋果", 2022, 130], ["蘋果", 2023, 140],
            ["鳳梨", 2020, 10],  ["鳳梨", 2021, 20],  ["鳳梨", 2022, 40],  ["鳳梨", 2023, 80],
            ["葡萄", 2020, 80],  ["葡萄", 2021, 75],  ["葡萄", 2022, 78],  ["葡萄", 2023, 80]
        ];
        const stmt = db.prepare("INSERT INTO 銷售資料 (產品名稱, 年份, 銷售額) VALUES (?, ?, ?);");
        for (const record of inserts) {
            stmt.run(record);
        }
        stmt.finalize();

        // 步驟 2: 使用 pivot_vtab 建立虛擬資料表
        db.run(`
            CREATE VIRTUAL TABLE v_銷售資料 USING pivot_vtab (
                (SELECT DISTINCT 產品名稱 FROM 銷售資料),
                (SELECT DISTINCT 年份, 年份 FROM 銷售資料 ORDER BY 年份),
                (SELECT sum(銷售額) FROM 銷售資料 WHERE 產品名稱 = ?1 AND 年份 = ?2)
            );
        `);

        // 步驟 3: 查詢虛擬資料表以獲取樞紐分析結果
        db.all("SELECT * FROM v_銷售資料;", (err, rows) => {
            if (err) {
                console.error("錯誤：查詢虛擬資料表失敗。", err);
                return;
            }
            console.log("樞紐分析執行結果：");
            console.table(rows);
        });
    });

    db.close();
});

三、`pivot_vtab` 虛擬資料表結構解析

CREATE VIRTUAL TABLE 陳述式是 pivot_vtab 的核心。其語法結構包含三個關鍵的子查詢參數，分別定義了樞紐表的列、欄與值。

列定義查詢 (Row Definition Query)
```
(SELECT DISTINCT 產品名稱 FROM 銷售資料)
```
此查詢定義了樞紐表的行標籤。其查詢結果集的第一個欄位（此處為 產品名稱）將構成樞紐分析表的第一列。
欄定義查詢 (Column Definition Query)
```
(SELECT DISTINCT 年份, 年份 FROM 銷售資料 ORDER BY 年份)
```
此查詢動態生成樞紐表的欄位。該查詢必須返回兩個欄位：第一個欄位的值將用於內部參數繫結（?2），第二個欄位的值將作為樞紐表新欄位的標題。ORDER BY 子句確保了欄位的邏輯順序。
值定義查詢 (Value Definition Query)
```
(SELECT sum(銷售額) FROM 銷售資料 WHERE 產品名稱 = ?1 AND 年份 = ?2)
```
此查詢是計算樞紐表各儲存格（Cell）值的核心邏輯。pivot_vtab 模組會遍歷由前兩個查詢定義的行列組合，並為每個組合執行此查詢。查詢中的佔位符 ?1 和 ?2 分別繫結：
- ?1: 當前行的值（來自列定義查詢，如 '蘋果'）。
- ?2: 當前欄的值（來自欄定義查詢，如 2020）。
此處使用 sum() 彙總函式，以確保在源表中存在多筆符合條件的記錄時，能夠正確地進行數值聚合。

四、執行結果與分析

執行上述程式碼後，將得到一個結構化的寬格式結果集，準確反映了每個產品在各年份的總銷售額。console.table 的輸出清晰地展示了資料維度的成功轉換。

樞紐分析執行結果：
┌─────────┬──────────┬──────┬──────┬──────┬──────┐
│ (index) │  產品名稱  │ 2020 │ 2021 │ 2022 │ 2023 │
├─────────┼──────────┼──────┼──────┼──────┼──────┤
│    0    │   '蘋果'   │ 100  │ 120  │ 130  │ 140  │
│    1    │   '鳳梨'   │  10  │  20  │  40  │  80  │
│    2    │   '葡萄'   │  80  │  75  │  78  │  80  │
└─────────┴──────────┴──────┴──────┴──────┴──────┘

五、`pivot_vtab` 的技術優勢

語法簡潔性 相較於使用多重 CASE WHEN ... THEN ... 結構的傳統 SQL 樞紐分析，pivot_vtab 的宣告式語法顯著提升了程式碼的可讀性與可維護性。
執行效能 作為一個以 C 語言實現的原生擴充功能，pivot_vtab 將計算密集的樞紐邏輯在資料庫內核層級完成。這避免了在應用層（如 Node.js）進行大規模資料集的拉取和處理，從而減少了網路 I/O 與應用層的 CPU 負擔，通常具有更優的執行效能。
動態適應性 樞紐表的欄位是透過查詢動態生成的。當源表中出現新的年份或產品類別時，無需修改 CREATE VIRTUAL TABLE 的定義。樞紐表會自動擴展以包含新的維度，此特性對需要自動生成報表的系統尤為重要。

六、環境建置與擴充功能載入

要使用 pivot_vtab，需先取得其原始碼（pivot.c），該檔案為 SQLite 官方發行版的一部分。開發者需根據目標作業系統平台，將其編譯為對應的動態連結函式庫（例如，macOS 的 .dylib、Linux 的 .so 或 Windows 的 .dll），並在應用程式中透過 db.loadExtension() 方法進行載入。

七、結論

SQLite 的 pivot_vtab 擴充功能為資料庫內的樞紐分析提供了一個功能強大、效能卓越且語法優雅的解決方案。它將複雜的資料重塑邏輯抽象為簡單的 SQL 宣告，使開發人員能更專注於業務邏輯本身。在需要進行資料透視的應用場景中，pivot_vtab 是一個值得優先考慮的技術選項。

摘要